using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VBABEW
    {
        public void SetUpNewRow(ref DataRow dwReturn)
        {

            switch (app.company)
            {
                default:
                case "JLT":
                    dwReturn["BBMANDANT"] = app.Mandant;
                    dwReturn["BBJJJJ"] = "2014";
                    dwReturn["BBBELEG"] = "146235";
                    dwReturn["BBLFDNR"] = "1";
                    dwReturn["BBKART"] = "J4-0003             ";
                    dwReturn["BBKVAR"] = "1 ";
                    dwReturn["BBKFARBE"] = "1         ";
                    dwReturn["BBZUSTAND"] = "AP  ";
                    dwReturn["BBBAUMKRS"] = "  ";
                    dwReturn["BBBAUMLFD"] = "000000";
                    dwReturn["BBSPSTA"] = "  ";
                    dwReturn["BBSPGRD"] = "  ";
                    dwReturn["BBZENSUR"] = "1";
                    dwReturn["BBTAX"] = "   ";
                    dwReturn["BBLO"] = "01";
                    dwReturn["BBLB"] = "  ";
                    dwReturn["BBLPLATZ"] = "        ";
                    dwReturn["BBAUFBKZ"] = " ";
                    dwReturn["BBKOLLINR"] = "        ";
                    dwReturn["BBKETTTYP"] = "      ";
                    dwReturn["BBPARTIE"] = "       ";
                    dwReturn["BBPARTIEKZ"] = " ";
                    dwReturn["BBAUFTYP"] = " ";
                    dwReturn["BBAUFKRS"] = "  ";
                    dwReturn["BBAUFLFD"] = "999999";
                    dwReturn["BBAUFPOS"] = "000";
                    dwReturn["BBAFOLGE"] = "000";
                    dwReturn["BBTEILNR"] = "0000";
                    dwReturn["BBSA"] = "   ";
                    dwReturn["BBSCHIENE"] = "     ";
                    dwReturn["BBMNRKRS"] = "  ";
                    dwReturn["BBMLFDNR"] = "0000";
                    dwReturn["BBMPOS"] = "00";
                    dwReturn["BBSCHICHT"] = "0";
                    dwReturn["BBDL"] = " ";
                    dwReturn["BBKSTKRS"] = "  ";
                    dwReturn["BBKST"] = "0000";
                    dwReturn["BBKOSTART"] = "      ";
                    dwReturn["BBBS"] = "62";
                    dwReturn["BBWERT"] = "0";
                    dwReturn["BBWERTDAT"] = "20140904";
                    dwReturn["BBWEKORR"] = "  ";
                    dwReturn["BBMTR"] = "5213";
                    dwReturn["BBGEW"] = "0";
                    dwReturn["BBMTRNOZU"] = "0";
                    dwReturn["BBGEWNOZU"] = "0";
                    dwReturn["BBBUCHDAT"] = "20140904";
                    dwReturn["BBBUCHZEIT"] = "105315";
                    dwReturn["BBBUCHBEN"] = "BATCHE  ";
                    dwReturn["BBFC"] = "BID09   ";
                    dwReturn["BBMATABR"] = "J";
                    dwReturn["BBINVBLG"] = "0";
                    dwReturn["BBKONDAT"] = "0";
                    dwReturn["BBSTORKZ"] = " ";
                    dwReturn["BBDISPOKZ"] = " ";
                    dwReturn["BBFILLER"] = "                                           ";
                    break;
            }
        }
    
        public void InsertBABEW(DataRow dwData, string ITXDBVERSION = "50.24")
        {
           
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                default:
                case "JLT":
                    or1.CommandText = "INSERT INTO BABEW ( BBMANDANT,BBJJJJ,BBBELEG,BBLFDNR,BBKART,BBKVAR,BBKFARBE,BBZUSTAND,BBBAUMKRS,BBBAUMLFD,BBSPSTA,BBSPGRD,BBZENSUR,BBTAX,BBLO,BBLB,BBLPLATZ,BBAUFBKZ,BBKOLLINR,BBKETTTYP,BBPARTIE,BBPARTIEKZ,BBAUFTYP,BBAUFKRS,BBAUFLFD,BBAUFPOS,BBAFOLGE,BBTEILNR,BBSA,BBSCHIENE,BBMNRKRS,BBMLFDNR,BBMPOS,BBSCHICHT,BBDL,BBKSTKRS,BBKST,BBKOSTART,BBBS,BBWERT,BBWERTDAT,BBWEKORR,BBMTR,BBGEW,BBMTRNOZU,BBGEWNOZU,BBBUCHDAT,BBBUCHZEIT,BBBUCHBEN,BBFC,BBMATABR,BBINVBLG,BBKONDAT,BBSTORKZ,BBDISPOKZ,BBFILLER)"
                   + " VALUES (:BBMANDANT:,:BBJJJJ:,:BBBELEG:,:BBLFDNR:,:BBKART:,:BBKVAR:,:BBKFARBE:,:BBZUSTAND:,:BBBAUMKRS:,:BBBAUMLFD:,:BBSPSTA:,:BBSPGRD:,:BBZENSUR:,:BBTAX:,:BBLO:,:BBLB:,:BBLPLATZ:,:BBAUFBKZ:,:BBKOLLINR:,:BBKETTTYP:,:BBPARTIE:,:BBPARTIEKZ:,:BBAUFTYP:,:BBAUFKRS:,:BBAUFLFD:,:BBAUFPOS:,:BBAFOLGE:,:BBTEILNR:,:BBSA:,:BBSCHIENE:,:BBMNRKRS:,:BBMLFDNR:,:BBMPOS:,:BBSCHICHT:,:BBDL:,:BBKSTKRS:,:BBKST:,:BBKOSTART:,:BBBS:,:BBWERT:,:BBWERTDAT:,:BBWEKORR:,:BBMTR:,:BBGEW:,:BBMTRNOZU:,:BBGEWNOZU:,:BBBUCHDAT:,:BBBUCHZEIT:,:BBBUCHBEN:,:BBFC:,:BBMATABR:,:BBINVBLG:,:BBKONDAT:,:BBSTORKZ:,:BBDISPOKZ:,:BBFILLER:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData); 
                    break;
            } 
            Connect2Proxy(or1);
        }
        public void DeleteBABEW(string sdkey, string key)
        {

            or1 = OracleDB.CreateCommand();
            Connect2Proxy(or1);
        }
        public DataSet QueryBABEW(DataRow dwData, int option = 1)
        {
           
            or1 = OracleDB.CreateCommand();
            string ZH = dwData["BBBAUMLFD"].ToString().Trim();
            string FROMDATE = dwData["BBWERTDAT"].ToString().Trim();
            string ENDDATE = dwData["BBBUCHDAT"].ToString().Trim();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM BABEW WHERE  ROWNUM<2 ";
                    break;
                case 1:
                    or1.CommandText = "SELECT*FROM BABEW WHERE "
                        + " BBMANDANT=:BBMANDANT: AND BBLFDNR=:BBLFDNR:  AND BBBELEG=:BBBELEG: ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 2:
                    or1.CommandText = "SELECT*FROM BABEW WHERE "
                    + " BBMANDANT=:BBMANDANT: AND BBAUFKRS=:BBAUFKRS:  AND BBAUFLFD=:BBAUFLFD: ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 4:
                    or1.CommandText = "SELECT ABPPOSTYP,MAX(ABPSOLLMENGE1) AS QTYPLAN , MAX(ABPSOLLMENGE7) AS PCSPLAN,MAX(ABPSTKLG) AS ABPSTKLG,ABPAUFKRS,ABPAUFLFD,ABPAUFPOS, BBBUCHDAT AS MDATE,SUM(BBMTR*0.1) AS QTY"
                    + " FROM ABPOS JOIN BABEW ON ABPMANDANT=BBMANDANT AND ABPAUFKRS=BBAUFKRS AND ABPAUFLFD=BBAUFLFD AND ABPAUFPOS=BBAUFPOS "
                    + " WHERE BBMANDANT=:BBMANDANT: AND BBAUFKRS=:BBAUFKRS: AND BBAUFLFD=:BBAUFLFD: AND BBAUFPOS=:BBAUFPOS: AND BBBS=:BBBS:"
                    + " GROUP BY ABPPOSTYP,ABPAUFKRS,ABPAUFLFD,ABPAUFPOS, BBBUCHDAT";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 5:
                    or1.CommandText = "select A.BMKART,A.BMFARBE,B.BBBAUMKRS,B.BBBAUMLFD,A.BMSOLLLG * 0.1 AS MTR,B.BBBUCHDAT,A.BMLO,A.BMLB,A.BMLP from BABST A, BABEW B"
                    + " where A.BMMANDANT = B.BBMANDANT AND A.BMBAUMKRS = B.BBBAUMKRS AND A.BMBAUMLFD = B.BBBAUMLFD"
                    + " AND A.BMLO = '01' AND A.BMLB = 'ZZ' AND A.BMLP = '01      '"
                    + " AND B.BBBS = '20' AND B.BBLO = '01' AND B.BBLB = 'QZ' AND B.BBLPLATZ = '01      '"
                    + " AND (B.BBBAUMLFD = '" + ZH + "' OR '" + ZH + "' = '000000') "
                    + " AND B.BBBUCHDAT >='" + FROMDATE + "' AND B.BBBUCHDAT <= '" + ENDDATE + "' ";
                    break;
                default:
                    break;
            }
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = "BABEW"; 
            return SResult;
        }
        public void UpdateBABEW(DataRow dwData, string ITXDBVERSION = "50.24")
        { 
            or1 = OracleDB.CreateCommand();

            #region====Command====
            or1.CommandText = "UPDATE BABEW SET  "
                + "BBMANDANT=:BBMANDANT,"
                + "BBJJJJ=:BBJJJJ,"
                + "BBBELEG=:BBBELEG,"
                + "BBLFDNR=:BBLFDNR,"
                + "BBKART=:BBKART,"
                + "BBKVAR=:BBKVAR,"
                + "BBKFARBE=:BBKFARBE,"
                + "BBZUSTAND=:BBZUSTAND,"
                + "BBBAUMKRS=:BBBAUMKRS,"
                + "BBBAUMLFD=:BBBAUMLFD,"
                + "BBSPSTA=:BBSPSTA,"
                + "BBSPGRD=:BBSPGRD,"
                + "BBZENSUR=:BBZENSUR,"
                + "BBTAX=:BBTAX,"
                + "BBLO=:BBLO,"
                + "BBLB=:BBLB,"
                + "BBLPLATZ=:BBLPLATZ,"
                + "BBAUFBKZ=:BBAUFBKZ,"
                + "BBKOLLINR=:BBKOLLINR,"
                + "BBKETTTYP=:BBKETTTYP,"
                + "BBPARTIE=:BBPARTIE,"
                + "BBPARTIEKZ=:BBPARTIEKZ,"
                + "BBAUFTYP=:BBAUFTYP,"
                + "BBAUFKRS=:BBAUFKRS,"
                + "BBAUFLFD=:BBAUFLFD,"
                + "BBAUFPOS=:BBAUFPOS,"
                + "BBAFOLGE=:BBAFOLGE,"
                + "BBTEILNR=:BBTEILNR,"
                + "BBSA=:BBSA,"
                + "BBSCHIENE=:BBSCHIENE,"
                + "BBMNRKRS=:BBMNRKRS,"
                + "BBMLFDNR=:BBMLFDNR,"
                + "BBMPOS=:BBMPOS,"
                + "BBSCHICHT=:BBSCHICHT,"
                + "BBDL=:BBDL,"
                + "BBKSTKRS=:BBKSTKRS,"
                + "BBKST=:BBKST,"
                + "BBKOSTART=:BBKOSTART,"
                + "BBBS=:BBBS,"
                + "BBWERT=:BBWERT,"
                + "BBWERTDAT=:BBWERTDAT,"
                + "BBWEKORR=:BBWEKORR,"
                + "BBMTR=:BBMTR,"
                + "BBGEW=:BBGEW,"
                + "BBMTRNOZU=:BBMTRNOZU,"
                + "BBGEWNOZU=:BBGEWNOZU,"
                + "BBBUCHDAT=:BBBUCHDAT,"
                + "BBBUCHZEIT=:BBBUCHZEIT,"
                + "BBBUCHBEN=:BBBUCHBEN,"
                + "BBFC=:BBFC,"
                + "BBMATABR=:BBMATABR,"
                + "BBINVBLG=:BBINVBLG,"
                + "BBKONDAT=:BBKONDAT,"
                + "BBSTORKZ=:BBSTORKZ,"
                + "BBDISPOKZ=:BBDISPOKZ,"
                + "BBFILLER=:BBFILLER "
               + " WHERE  "
               + " BBMANDANT=:BBMANDANT AND BBBELEG=:BBBELEG AND BBLFDNR=:BBLFDNR";
            #endregion

            #region====Parameters====

            or1.Parameters.Add("BBMANDANT", OracleType.NVarChar).Value = dwData["BBMANDANT"].ToString();
            or1.Parameters.Add("BBJJJJ", OracleType.NVarChar).Value = dwData["BBJJJJ"].ToString();
            or1.Parameters.Add("BBBELEG", OracleType.NVarChar).Value = dwData["BBBELEG"].ToString();
            or1.Parameters.Add("BBLFDNR", OracleType.NVarChar).Value = dwData["BBLFDNR"].ToString();
            or1.Parameters.Add("BBKART", OracleType.NVarChar).Value = dwData["BBKART"].ToString();
            or1.Parameters.Add("BBKVAR", OracleType.NVarChar).Value = dwData["BBKVAR"].ToString();
            or1.Parameters.Add("BBKFARBE", OracleType.NVarChar).Value = dwData["BBKFARBE"].ToString();
            or1.Parameters.Add("BBZUSTAND", OracleType.NVarChar).Value = dwData["BBZUSTAND"].ToString();
            or1.Parameters.Add("BBBAUMKRS", OracleType.NVarChar).Value = dwData["BBBAUMKRS"].ToString();
            or1.Parameters.Add("BBBAUMLFD", OracleType.NVarChar).Value = dwData["BBBAUMLFD"].ToString();
            or1.Parameters.Add("BBSPSTA", OracleType.NVarChar).Value = dwData["BBSPSTA"].ToString();
            or1.Parameters.Add("BBSPGRD", OracleType.NVarChar).Value = dwData["BBSPGRD"].ToString();
            or1.Parameters.Add("BBZENSUR", OracleType.NVarChar).Value = dwData["BBZENSUR"].ToString();
            or1.Parameters.Add("BBTAX", OracleType.NVarChar).Value = dwData["BBTAX"].ToString();
            or1.Parameters.Add("BBLO", OracleType.NVarChar).Value = dwData["BBLO"].ToString();
            or1.Parameters.Add("BBLB", OracleType.NVarChar).Value = dwData["BBLB"].ToString();
            or1.Parameters.Add("BBLPLATZ", OracleType.NVarChar).Value = dwData["BBLPLATZ"].ToString();
            or1.Parameters.Add("BBAUFBKZ", OracleType.NVarChar).Value = dwData["BBAUFBKZ"].ToString();
            or1.Parameters.Add("BBKOLLINR", OracleType.NVarChar).Value = dwData["BBKOLLINR"].ToString();
            or1.Parameters.Add("BBKETTTYP", OracleType.NVarChar).Value = dwData["BBKETTTYP"].ToString();
            or1.Parameters.Add("BBPARTIE", OracleType.NVarChar).Value = dwData["BBPARTIE"].ToString();
            or1.Parameters.Add("BBPARTIEKZ", OracleType.NVarChar).Value = dwData["BBPARTIEKZ"].ToString();
            or1.Parameters.Add("BBAUFTYP", OracleType.NVarChar).Value = dwData["BBAUFTYP"].ToString();
            or1.Parameters.Add("BBAUFKRS", OracleType.NVarChar).Value = dwData["BBAUFKRS"].ToString();
            or1.Parameters.Add("BBAUFLFD", OracleType.NVarChar).Value = dwData["BBAUFLFD"].ToString();
            or1.Parameters.Add("BBAUFPOS", OracleType.NVarChar).Value = dwData["BBAUFPOS"].ToString();
            or1.Parameters.Add("BBAFOLGE", OracleType.NVarChar).Value = dwData["BBAFOLGE"].ToString();
            or1.Parameters.Add("BBTEILNR", OracleType.NVarChar).Value = dwData["BBTEILNR"].ToString();
            or1.Parameters.Add("BBSA", OracleType.NVarChar).Value = dwData["BBSA"].ToString();
            or1.Parameters.Add("BBSCHIENE", OracleType.NVarChar).Value = dwData["BBSCHIENE"].ToString();
            or1.Parameters.Add("BBMNRKRS", OracleType.NVarChar).Value = dwData["BBMNRKRS"].ToString();
            or1.Parameters.Add("BBMLFDNR", OracleType.NVarChar).Value = dwData["BBMLFDNR"].ToString();
            or1.Parameters.Add("BBMPOS", OracleType.NVarChar).Value = dwData["BBMPOS"].ToString();
            or1.Parameters.Add("BBSCHICHT", OracleType.NVarChar).Value = dwData["BBSCHICHT"].ToString();
            or1.Parameters.Add("BBDL", OracleType.NVarChar).Value = dwData["BBDL"].ToString();
            or1.Parameters.Add("BBKSTKRS", OracleType.NVarChar).Value = dwData["BBKSTKRS"].ToString();
            or1.Parameters.Add("BBKST", OracleType.NVarChar).Value = dwData["BBKST"].ToString();
            or1.Parameters.Add("BBKOSTART", OracleType.NVarChar).Value = dwData["BBKOSTART"].ToString();
            or1.Parameters.Add("BBBS", OracleType.NVarChar).Value = dwData["BBBS"].ToString();
            or1.Parameters.Add("BBWERT", OracleType.NVarChar).Value = dwData["BBWERT"].ToString();
            or1.Parameters.Add("BBWERTDAT", OracleType.NVarChar).Value = dwData["BBWERTDAT"].ToString();
            or1.Parameters.Add("BBWEKORR", OracleType.NVarChar).Value = dwData["BBWEKORR"].ToString();
            or1.Parameters.Add("BBMTR", OracleType.NVarChar).Value = dwData["BBMTR"].ToString();
            or1.Parameters.Add("BBGEW", OracleType.NVarChar).Value = dwData["BBGEW"].ToString();
            or1.Parameters.Add("BBMTRNOZU", OracleType.NVarChar).Value = dwData["BBMTRNOZU"].ToString();
            or1.Parameters.Add("BBGEWNOZU", OracleType.NVarChar).Value = dwData["BBGEWNOZU"].ToString();
            or1.Parameters.Add("BBBUCHDAT", OracleType.NVarChar).Value = dwData["BBBUCHDAT"].ToString();
            or1.Parameters.Add("BBBUCHZEIT", OracleType.NVarChar).Value = dwData["BBBUCHZEIT"].ToString();
            or1.Parameters.Add("BBBUCHBEN", OracleType.NVarChar).Value = dwData["BBBUCHBEN"].ToString();
            or1.Parameters.Add("BBFC", OracleType.NVarChar).Value = dwData["BBFC"].ToString();
            or1.Parameters.Add("BBMATABR", OracleType.NVarChar).Value = dwData["BBMATABR"].ToString();
            or1.Parameters.Add("BBINVBLG", OracleType.NVarChar).Value = dwData["BBINVBLG"].ToString();
            or1.Parameters.Add("BBKONDAT", OracleType.NVarChar).Value = dwData["BBKONDAT"].ToString();
            or1.Parameters.Add("BBSTORKZ", OracleType.NVarChar).Value = dwData["BBSTORKZ"].ToString();
            or1.Parameters.Add("BBDISPOKZ", OracleType.NVarChar).Value = dwData["BBDISPOKZ"].ToString();
            or1.Parameters.Add("BBFILLER", OracleType.NVarChar).Value = dwData["BBFILLER"].ToString();
            #endregion

            Connect2Proxy(or1);
        }
    }
}
